GET /v2/products/any-day-tickets

[!info] 概述 获取当前用户的 ANY_DAY_TICKET（任意日门票）产品列表，支持关键词搜索、状态筛选、排序和分页。

[!tip] 业务场景 ANY_DAY_TICKET 是一种特殊的门票类型，在购买时才绑定到具体活动，而非创建时。适用于现场售票、待定活动等灵活场景。

请求信息

请求地址

GET /v2/products/any-day-tickets

请求头 (Headers)

请求参数 (Query Parameters)

SortOrder 枚举

enum SortOrder {
  ASC = 'asc',   // 升序
  DESC = 'desc'  // 降序
}

ProductStatus 枚举

enum ProductStatus {
  ACTIVE = 'ACTIVE',           // 活跃
  DRAFT = 'DRAFT',             // 草稿
  DELISTED = 'DELISTED',       // 已下架
  ARCHIVED = 'ARCHIVED'        // 已归档
}

ProductLifecycleStatus 枚举

enum ProductLifecycleStatus {
  LIFECYCLE_STATUS_NORMAL = 'NORMAL',           // 正常
  LIFECYCLE_STATUS_ON_HOLD = 'ON_HOLD',         // 暂停
  LIFECYCLE_STATUS_CANCELED = 'CANCELED',       // 取消
  LIFECYCLE_STATUS_ARCHIVED = 'ARCHIVED'        // 归档
}

响应结构

响应格式

[
  ProductResponse[],  // 产品列表
  number             // 总数量
]

ProductResponse 字段说明

ProductVariantResponse 字段

成功示例

请求示例 (cURL)

curl 'https://release.katana-api.1m.app/v2/products/any-day-tickets?keyword=&sortCreatedAt=desc&pageSize=30&pageNumber=1' \
  -H 'accept: application/json, text/plain, */*' \
  -H 'authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...' \
  -H 'timezone: Asia/Shanghai'

响应示例

[
  [
    {
      "id": "018eaabc-1234-5678-9012-fedcba987654",
      "title": "At-Door General Admission",
      "listingType": "ANY_DAY_TICKET",
      "status": "ACTIVE",
      "externalStatus": "ACTIVE",
      "followExternalStatus": false,
      "platform": "PEAR",
      "catalog": {
        "id": "catalog-uuid-123",
        "name": "Event Catalog",
        "catalogType": "EVENT"
      },
      "eventId": null,
      "event": undefined,
      "bodyHtml": "<p>Valid for any upcoming event</p>",
      "bodyText": "Valid for any upcoming event",
      "coverImage": {
        "src": "https://cdn.example.com/cover.jpg",
        "mediaType": "IMAGE"
      },
      "variants": [
        {
          "id": "variant-uuid-1",
          "position": 1,
          "price": "50.00",
          "inventoryQuantity": 100,
          "title": "Standard Entry",
          "option": { "option1": "Standard" },
          "fees": 5.50,
          "transactionFee": {
            "transactionItemFee": 5.50,
            "platformFee": 1.00,
            "customFee": 0,
            "customFeeBreakdown": { "TAX": { "unitFixedFee": 0, "unitPercentageFee": 0.1 } }
          }
        }
      ],
      "priceMin": 50.00,
      "priceMax": 50.00,
      "deliveryMethod": "QR_CODE",
      "isAvailable": true,
      "taxEnable": true,
      "tags": ["ticket", "flexible"],
      "createdAt": "2026-03-01T10:00:00Z",
      "updatedAt": "2026-03-01T10:00:00Z",
      "isMultipleDaysPassEnabled": false,
      "multipleDaysPass": undefined,
      "atDoorTicketConfig": {
        "includeTitleText": null,
        "excludeTitleText": "VIP",
        "eventSelectionWindowHours": 24
      },
      "isProductFormEnabled": false,
      "productForm": null,
      "alias": "custom-alias-abc123"
    }
  ],
  42
]

错误示例

401 Unauthorized

{
  "statusCode": 401,
  "message": "Unauthorized",
  "error": "Unauthorized"
}

原因：未提供有效的 authorization header。

403 Forbidden

{
  "statusCode": 403,
  "message": "No permission",
  "error": "Forbidden"
}

原因：用户权限不足（非 BUSINESS_PARTNER 角色）。

500 Internal Server Error

{
  "statusCode": 500,
  "message": "Internal server error",
  "error": "Internal Server Error"
}

原因：服务器内部错误（如数据库连接失败、查询超时等）。

业务逻辑

核心流程

┌─────────────────────────────────────────────────────────────────┐
│  1. 获取用户 Event Catalog (自动创建)                          │
│     eventCatalog = eventCatalogService.ensureExists(userId)      │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  2. 自动设置 listingType = ANY_DAY_TICKET                      │
│     query.listingType = ProductType.ANY_DAY_TICKET              │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  3. 复用 MerchantProductsService 查询逻辑                       │
│     [products, total] = merchantProductsService                │
│       .findManyByMerchantId(eventCatalog.id, query)             │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  4. 批量加载关联数据（性能优化）                                │
│     - PromoterProduct.alias (产品别名)                          │
│     - User (用户信息)                                           │
│     - ProductFormSummary (产品表单)                             │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  5. 使用 AnyDayTicketProductStrategy 格式化响应                 │
│     formattedProducts = products.map(formatResponse)            │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  6. 添加 alias 字段并返回                                       │
│     return [formattedProductsWithAlias, total]                  │
└─────────────────────────────────────────────────────────────────┘

关键设计决策

1. 自动 Event Catalog

• ANY_DAY_TICKET 必须在 Event Catalog 中创建
• eventCatalogService.ensureExists(userId) 自动创建 Event Catalog（如不存在）
• 商家 merchantId 自动设置为用户的 Event Catalog ID

2. 复用 V1 查询逻辑

• 复用 MerchantProductsService.findManyByMerchantId()
• 支持所有现有筛选条件：keyword、isFeatured、sortCreatedAt 等
• 复用 V1 分页和排序逻辑

3. 批量加载优化

• 一次性加载所有关联数据（用户、别名、产品表单）
• 避免 N+1 查询问题
• 提升列表查询性能

4. 策略模式格式化

• 使用 AnyDayTicketProductStrategy.formatResponse() 格式化响应
• 确保响应格式与产品类型一致
• 支持产品表单（KAT-8634）和门票筛选配置（KAT-10412）

与 TICKET 的关键差异

数据库设计

核心表结构

1. Product 表 (产品主表)

CREATE TABLE "Product" (
  id                        UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  remote_id                 VARCHAR(255),
  platform                  VARCHAR(50),

  -- 基础信息
  merchant_id               UUID NOT NULL,
  title                     VARCHAR(255) NOT NULL,
  handle                    VARCHAR(255),
  product_type              VARCHAR(50),
  listing_type              VARCHAR(50) DEFAULT 'REGULAR',  -- ANY_DAY_TICKET

  -- 内容
  body_html                 TEXT NOT NULL,
  original_body_html        TEXT NOT NULL,
  is_body_html_overridden   BOOLEAN DEFAULT false,
  body_text                 TEXT DEFAULT '',
  body_json                 TEXT,

  -- 状态
  status                    VARCHAR(50) DEFAULT 'ACTIVE',
  external_status           VARCHAR(50) DEFAULT 'ACTIVE',
  follow_external_status    BOOLEAN DEFAULT true,
  lifecycle_status          VARCHAR(50) DEFAULT 'LIFECYCLE_STATUS_NORMAL',

  -- 交付与配送
  delivery_method           VARCHAR(50),
  shipping_type             VARCHAR(50) DEFAULT 'INHERIT_SHIPPING',
  additional_shipping_fee   FLOAT DEFAULT 0,
  auto_fulfill              BOOLEAN DEFAULT false,

  -- 定价 (已弃用，保留兼容)
  price_min                 FLOAT DEFAULT 0.0,
  price_max                 FLOAT DEFAULT 0.0,
  price_min_anchor          FLOAT DEFAULT 0.0,
  price_max_anchor          FLOAT DEFAULT 0.0,

  -- 库存
  inventory_quantity        INT DEFAULT 0,
  inventory_quantity_original INT DEFAULT 0,
  sold_quantity             INT DEFAULT 0,

  -- 标记
  delisted                  BOOLEAN DEFAULT false,
  is_featured               BOOLEAN DEFAULT false,
  featured_score            INT,
  is_available              BOOLEAN DEFAULT true,
  is_used                   VARCHAR(50) DEFAULT 'NWT',

  -- 税收
  tax_enable                BOOLEAN DEFAULT false,
  tax_jar_category          VARCHAR(255),
  override_event_tax        BOOLEAN DEFAULT false,  -- KAT-10224
  custom_tax_rate           FLOAT,                   -- KAT-10224

  -- ANY_DAY_TICKET 特有字段
  is_multiple_days_pass_enabled BOOLEAN DEFAULT false,
  multiple_days_pass        JSONB,
  at_door_ticket_config     JSONB,                 -- KAT-10412
  exclude_from_post_sync    BOOLEAN DEFAULT false,  -- KAT-10459
  allow_at_door_sales       BOOLEAN DEFAULT false,

  -- 产品表单 (KAT-8634)
  is_product_form_enabled   BOOLEAN DEFAULT false,

  -- 其他
  tags                      JSONB,
  vendor                    VARCHAR(255),
  commission_rate           FLOAT,
  cover_image               JSONB,
  extra_info                JSONB,
  custom_fields_info        JSONB,
  shipping_options          JSONB,
  shipping_note             TEXT,
  third_party_delivery_message TEXT,                -- KAT-9452

  -- Event 关联 (ANY_DAY_TICKET 始终为 NULL)
  event_id                  UUID,

  -- 时间戳
  created_at                TIMESTAMPTZ DEFAULT now(),
  updated_at                TIMESTAMPTZ DEFAULT now(),
  published_at              TIMESTAMPTZ,
  sync_at                   TIMESTAMPTZ,
  deleted_at                TIMESTAMPTZ,

  -- 外键
  CONSTRAINT "Product_merchant_id_fkey" FOREIGN KEY (merchant_id) REFERENCES "Merchant"(id),
  CONSTRAINT "Product_event_id_fkey" FOREIGN KEY (event_id) REFERENCES "Event"(id),
  CONSTRAINT "Product_product_form_id_fkey" FOREIGN KEY (id) REFERENCES "UserContactForm"(product_id)
);

-- 索引
CREATE INDEX "Product_merchant_id_idx" ON "Product"(merchant_id);
CREATE INDEX "Product_listing_type_idx" ON "Product"(listing_type);
CREATE INDEX "Product_event_id_idx" ON "Product"(event_id);
CREATE INDEX "Product_deleted_at_idx" ON "Product"(deleted_at);
CREATE INDEX "Product_status_idx" ON "Product"(status);
CREATE INDEX "Product_is_featured_idx" ON "Product"(is_featured);
CREATE INDEX "Product_created_at_idx" ON "Product"(created_at);

2. ProductVariant 表 (产品变体表)

CREATE TABLE "ProductVariant" (
  id                           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  remote_id                    VARCHAR(255),

  -- 关联
  product_id                   UUID NOT NULL,
  inventory_item_id            VARCHAR(255) UNIQUE,

  -- 基本信息
  title                        VARCHAR(255),
  position                     INT,
  sku                          VARCHAR(255),
  option                       JSONB NOT NULL DEFAULT '{}',

  -- 定价
  price                        VARCHAR(255) NOT NULL,
  price_anchor                 VARCHAR(255) DEFAULT '0.00',
  price_imported               VARCHAR(255) DEFAULT '0.00',
  compare_at_price             VARCHAR(255),

  -- 库存
  inventory_quantity           INT DEFAULT 0,
  inventory_quantity_original   INT DEFAULT 0,
  inventory_management         VARCHAR(50),
  inventory_policy             VARCHAR(50) DEFAULT 'DENY',
  sold_quantity                INT DEFAULT 0,

  -- ANY_DAY_TICKET 专属字段
  ticket_price                 FLOAT DEFAULT 0.0,
  fees                         FLOAT DEFAULT 0.0,
  transaction_fee              JSONB DEFAULT '{}',

  -- 购买数量限制 (KAT-9346)
  is_min_purchase_quantity_enabled BOOLEAN DEFAULT false,
  min_purchase_quantity        INT,
  is_max_purchase_quantity_enabled BOOLEAN DEFAULT false,
  max_purchase_quantity        INT,
  is_pack_size_enabled         BOOLEAN DEFAULT false,
  pack_size                    INT,

  -- 配送
  fulfillment_service         VARCHAR(255) DEFAULT 'manual',
  grams                        INT DEFAULT 0,
  weight                       INT,
  weight_unit                  VARCHAR(50),

  -- 图片
  image_id                     UUID,
  image_ids                    TEXT[] DEFAULT ARRAY[]::TEXT[],

  -- 其他
  tax_code                     VARCHAR(255),
  taxable                      BOOLEAN,
  platform                     VARCHAR(50),

  -- 时间戳
  created_at                   TIMESTAMPTZ DEFAULT now(),
  updated_at                   TIMESTAMPTZ DEFAULT now(),

  -- 外键
  CONSTRAINT "ProductVariant_product_id_fkey" FOREIGN KEY (product_id) REFERENCES "Product"(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX "ProductVariant_product_id_idx" ON "ProductVariant"(product_id);
CREATE INDEX "ProductVariant_remote_id_idx" ON "ProductVariant"(remote_id);

3. PromoterProduct 表 (推广者产品关联表)

CREATE TABLE "PromoterProduct" (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- 关联
  promoter_id       UUID NOT NULL,
  merchant_product_id UUID NOT NULL,

  -- 别名 (用于推广者自定义产品名称)
  alias             VARCHAR(255) DEFAULT '',

  -- 封面图片
  cover_image       JSONB,

  -- 标记
  is_my_item        BOOLEAN DEFAULT false,

  -- 时间戳
  pinned_at         TIMESTAMPTZ,
  created_at        TIMESTAMPTZ DEFAULT now(),
  updated_at        TIMESTAMPTZ DEFAULT now(),
  deleted_at        TIMESTAMPTZ,

  -- 外键
  CONSTRAINT "PromoterProduct_promoter_id_fkey" FOREIGN KEY (promoter_id) REFERENCES "User"(id),
  CONSTRAINT "PromoterProduct_merchant_product_id_fkey" FOREIGN KEY (merchant_product_id) REFERENCES "Product"(id) ON DELETE CASCADE,

  -- 唯一约束 (同一推广者下别名唯一)
  CONSTRAINT "PromoterProduct_promoter_id_alias_key" UNIQUE (promoter_id, alias)
);

-- 索引
CREATE INDEX "PromoterProduct_promoter_id_idx" ON "PromoterProduct"(promoter_id);
CREATE INDEX "PromoterProduct_merchant_product_id_idx" ON "PromoterProduct"(merchant_product_id);

4. Merchant 表 (商家/目录表)

CREATE TABLE "Merchant" (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  invitation_id     VARCHAR(255) UNIQUE,

  -- 基本信息
  store_name        VARCHAR(255),
  description       TEXT,
  logo              TEXT,
  bio               TEXT,

  -- 目录类型
  catalog_type      VARCHAR(50) DEFAULT 'GENERAL',  -- 'GENERAL' 或 'EVENT'

  -- 配送策略
  shipping_fee      FLOAT DEFAULT 4.99,
  delivery_time     INT[] DEFAULT ARRAY[0, 5]::INT[],
  shipping_policy   JSONB,

  -- 退货政策
  return_policy     TEXT DEFAULT 'No return accepted',
  return_policy_tag VARCHAR(255),

  -- 佣金
  commission_rate   FLOAT DEFAULT 25,

  -- 平台
  type              VARCHAR(50) DEFAULT 'PEAR_DEFAULT',
  platform          VARCHAR(50),
  shop_url          VARCHAR(255),
  display_shop_url  VARCHAR(255),

  -- 用户关联
  user_id           UUID,

  -- 时间戳
  created_at        TIMESTAMPTZ DEFAULT now(),
  updated_at        TIMESTAMPTZ DEFAULT now(),
  deleted_at        TIMESTAMPTZ,

  -- 外键
  CONSTRAINT "Merchant_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX "Merchant_user_id_idx" ON "Merchant"(user_id);
CREATE INDEX "Merchant_type_idx" ON "Merchant"(type);
CREATE INDEX "Merchant_platform_idx" ON "Merchant"(platform);

5. UserContactForm 表 (产品表单 - KAT-8634)

CREATE TABLE "UserContactForm" (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- 关联
  user_id           UUID NOT NULL,
  product_id        UUID UNIQUE,  -- KAT-8634: 产品表单关联

  -- 表单配置
  title             VARCHAR(255) NOT NULL,
  subtitle          VARCHAR(255),
  show_image        BOOLEAN DEFAULT false,
  position          INT,
  recipient_email   VARCHAR(255) NOT NULL,

  -- 确认消息 (KAT-10428)
  confirmation_message JSONB,

  -- 表单类型
  form_type         VARCHAR(50) DEFAULT 'USER_CONTACT',  -- 'USER_CONTACT' 或 'PRODUCT'

  -- 时间戳
  created_at        TIMESTAMPTZ DEFAULT now(),
  updated_at        TIMESTAMPTZ DEFAULT now(),
  deleted_at        TIMESTAMPTZ,

  -- 外键
  CONSTRAINT "UserContactForm_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "User"(id),
  CONSTRAINT "UserContactForm_product_id_fkey" FOREIGN KEY (product_id) REFERENCES "Product"(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX "UserContactForm_user_id_idx" ON "UserContactForm"(user_id);
CREATE INDEX "UserContactForm_product_id_idx" ON "UserContactForm"(product_id);

6. ContactFormField 表 (表单字段 - KAT-8634)

CREATE TABLE "ContactFormField" (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- 关联
  user_contact_form_id UUID NOT NULL,

  -- 字段配置
  title             VARCHAR(255) NOT NULL,
  field             VARCHAR(50) NOT NULL,  -- UserContactFormEnum
  required          BOOLEAN DEFAULT false,
  position          INT,
  description       VARCHAR(255),
  tooltip           VARCHAR(255),

  -- 扩展配置
  extensions        JSONB,  -- 字段类型特定配置

  -- 时间戳
  created_at        TIMESTAMPTZ DEFAULT now(),
  updated_at        TIMESTAMPTZ DEFAULT now(),
  deleted_at        TIMESTAMPTZ,

  -- 外键
  CONSTRAINT "ContactFormField_user_contact_form_id_fkey" FOREIGN KEY (user_contact_form_id) REFERENCES "UserContactForm"(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX "ContactFormField_user_contact_form_id_idx" ON "ContactFormField"(user_contact_form_id);

7. Event 表 (活动表 - 间接关联)

[!important] 设计说明 ANY_DAY_TICKET 在创建时不关联 Event（Product.event_id = NULL），但在购买时绑定（OrderLineItem.variantInfo.eventId）。Event 表用于：

• 前端展示可选活动列表 (GET /product-event/v2/upcoming)
• 验证活动状态（仅 UPCOMING 状态可选）
• atDoorTicketConfig 筛选规则（标题匹配、时间窗口）

CREATE TABLE "Event" (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  remote_id         VARCHAR(255),

  -- 基本信息
  curator_id        UUID NOT NULL,
  title             VARCHAR(255) NOT NULL,
  status            VARCHAR(50) NOT NULL,  -- UPCOMING, ON_HOLD, CANCELED, COMPLETED
  platform          VARCHAR(50) DEFAULT 'PEAR',

  -- 时间与地点
  start_date        TIMESTAMPTZ NOT NULL,
  end_date          TIMESTAMPTZ,
  start_date_display VARCHAR(255),
  end_date_display   VARCHAR(255),
  timezone          JSONB,
  venue             VARCHAR(255),
  location          VARCHAR(255),
  location_details   JSONB,
  country           VARCHAR(50) DEFAULT 'US',
  state             VARCHAR(50),

  -- 地址隐私控制 (KAT-8896, KAT-9075)
  is_address_reveal_enabled BOOLEAN DEFAULT false,
  address_reveal_config   JSONB,

  -- 税收配置 (KAT-9007)
  is_tax_enabled    BOOLEAN DEFAULT false,
  tax_config        JSONB,

  -- Media 文本配置 (KAT-10318)
  media_text_config JSONB,

  -- 描述
  description       TEXT,
  description_body_json TEXT,  -- Lexical JSON (KAT-10270)

  -- Lineup (嘉宾阵容)
  lineup            JSONB,

  -- 额外信息
  poster            JSONB,
  message_info      JSONB DEFAULT '{"smsMaxLimit": 3, "emailMaxLimit": 5, "cooldownMinutes": 15}',
  extra_info        JSONB,

  -- 自动完成
  allow_auto_complete BOOLEAN DEFAULT true,

  -- 自动替换 (KAT-9967)
  auto_replace      BOOLEAN DEFAULT false,

  -- 创建步骤追踪 (KAT-10448)
  creation_step     VARCHAR(50) DEFAULT 'ADD_EVENT_DETAILS',

  -- 时间戳
  created_at        TIMESTAMPTZ DEFAULT now(),
  updated_at        TIMESTAMPTZ DEFAULT now(),
  deleted_at        TIMESTAMPTZ,

  -- 外键
  CONSTRAINT "Event_curator_id_fkey" FOREIGN KEY (curator_id) REFERENCES "User"(id)
);

-- 索引
CREATE INDEX "Event_curator_id_idx" ON "Event"(curator_id);
CREATE INDEX "Event_status_idx" ON "Event"(status);
CREATE INDEX "Event_platform_idx" ON "Event"(platform);
CREATE INDEX "Event_start_date_idx" ON "Event"(start_date);

8. OrderLineItem 表 (订单明细表 - Event 绑定存储)

[!tip] 关键设计 ANY_DAY_TICKET 的 Event 绑定通过 variantInfo.eventId 在购买时存储，而非 Product 表。

-- OrderLineItem 表结构（简化版，仅展示相关字段）
CREATE TABLE "OrderLineItem" (
  id                UUID PRIMARY KEY,
  order_id          UUID NOT NULL,

  -- 产品关联
  product_id        UUID NOT NULL,
  product_variant_id UUID,

  -- ANY_DAY_TICKET Event 绑定 (购买时设置)
  -- variantInfo 结构: { eventId: "event-uuid", ... }
  variant_info      JSONB,

  -- 数量与定价
  quantity          INT NOT NULL,
  unit_price        FLOAT NOT NULL,
  line_item_price   FLOAT NOT NULL,

  -- ... 其他字段 ...

  -- 外键
  CONSTRAINT "OrderLineItem_product_id_fkey" FOREIGN KEY (product_id) REFERENCES "Product"(id)
);

-- 查询示例：获取 ANY_DAY_TICKET 订单的活动 ID
SELECT
  oli.id,
  oli.product_id,
  oli.variant_info->>'eventId' AS event_id,  -- 从 JSONB 提取 eventId
  e.title AS event_title,
  e.status AS event_status
FROM "OrderLineItem" oli
INNER JOIN "Product" p ON p.id = oli.product_id
LEFT JOIN "Event" e ON e.id = (oli.variant_info->>'eventId')
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND oli.variant_info->>'eventId' IS NOT NULL;  -- 仅查询有 Event 绑定的订单

表关系图 (ERD)

erDiagram
    Merchant ||--o{ Product : "包含"
    Product ||--o{ ProductVariant : "包含"
    Product ||--o| UserContactForm : "可选 (产品表单)"
    UserContactForm ||--o{ ContactFormField : "包含"
    Product ||--o{ PromoterProduct : "推广"
    User ||--o{ PromoterProduct : "拥有"
    Merchant }o--|| User : "拥有"

    -- Event 与 Product 的关联（仅 TICKET 类型）
    Event ||--o{ Product : "TICKET产品绑定"

    -- OrderLineItem 存储购买时的 Event 绑定
    Order ||--o{ OrderLineItem : "包含"
    Product ||--o{ OrderLineItem : "购买"

    -- ANY_DAY_TICKET 与 Event 的间接关联（虚线表示运行时绑定）
    Product ..|> OrderLineItem : "variantInfo.eventId (购买时)"
    OrderLineItem ..}o| Event : "variantInfo.eventId"

    Merchant {
        uuid id PK
        string catalog_type
        string store_name
    }

    Product {
        uuid id PK
        uuid merchant_id FK
        uuid event_id FK
        string listing_type
        string title
        jsonb at_door_ticket_config
        boolean is_product_form_enabled
    }

    ProductVariant {
        uuid id PK
        uuid product_id FK
        string price
        int inventory_quantity
        float fees
        jsonb transaction_fee
    }

    PromoterProduct {
        uuid id PK
        uuid promoter_id FK
        uuid merchant_product_id FK
        string alias
    }

    UserContactForm {
        uuid id PK
        uuid user_id FK
        uuid product_id FK
        string form_type
        string title
    }

    ContactFormField {
        uuid id PK
        uuid user_contact_form_id FK
        string field
        boolean required
        jsonb extensions
    }

    Event {
        uuid id PK
        uuid curator_id FK
        string title
        string status
        timestamptz start_date
        jsonb address_reveal_config
        jsonb tax_config
    }

    Order {
        uuid id PK
        string status
        float total_to_pay
    }

    OrderLineItem {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        jsonb variant_info
        int quantity
    }

[!note] ANY_DAY_TICKET 与 Event 的关联

• 创建时：Product.event_id = NULL（无直接关联）
• 购买时：OrderLineItem.variantInfo.eventId 存储选中的 Event ID
• 验证时：通过 GET /product-event/v2/upcoming 查询可选 Event 列表

JSONB 字段详解

atDoorTicketConfig (KAT-10412)

-- JSONB 结构示例
{
  "includeTitleText": "VIP",           -- 仅显示包含 "VIP" 的活动
  "excludeTitleText": "Private",        -- 排除包含 "Private" 的活动
  "eventSelectionWindowHours": 24       -- 仅显示 24 小时内的活动
}

transactionFee (ProductVariant)

-- JSONB 结构示例
{
  "transactionItemFee": 5.50,           -- 总手续费
  "platformFee": 1.00,                  -- 平台费用
  "customFee": 0.50,                    -- 自定义费用
  "customFeeBreakdown": {               -- 费用明细
    "TAX": {
      "unitFixedFee": 0,
      "unitPercentageFee": 0.1          -- 10% 税率
    }
  }
}

SQL 查询示例

1. 查询 ANY_DAY_TICKET 产品列表

-- 基础查询
SELECT
  p.id,
  p.title,
  p.listing_type,
  p.status,
  p.lifecycle_status,
  p.created_at,
  p.updated_at,
  p.inventory_quantity,
  p.price_min,
  p.price_max,
  p.delivery_method,
  p.at_door_ticket_config,
  p.is_product_form_enabled,
  -- 关联 PromoterProduct 别名
  COALESCE(pp.alias, '') AS alias,
  -- 目录信息
  m.id AS merchant_id,
  m.store_name AS catalog_name,
  m.catalog_type
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
LEFT JOIN "PromoterProduct" pp ON pp.merchant_product_id = p.id AND pp.deleted_at IS NULL
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND p.merchant_id = :merchant_id  -- 用户的 Event Catalog ID
ORDER BY
  p.created_at DESC
LIMIT :pageSize OFFSET :offset;

2. 带关键词搜索的查询

-- 关键词搜索 (搜索标题、正文、标签、类型、供应商)
SELECT DISTINCT
  p.id,
  p.title,
  p.listing_type,
  p.status
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND (
    p.title ILIKE '%' || :keyword || '%'
    OR p.body_html ILIKE '%' || :keyword || '%'
    OR p.tags::text ILIKE '%' || :keyword || '%'
    OR p.product_type ILIKE '%' || :keyword || '%'
    OR p.vendor ILIKE '%' || :keyword || '%'
  )
ORDER BY p.created_at DESC;

3. 带状态筛选的查询

-- 按 status 和 lifecycle_status 筛选
SELECT
  p.id,
  p.title,
  p.status,
  p.lifecycle_status,
  p.is_featured
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND p.status = ANY(:statusArray)  -- ['ACTIVE', 'DRAFT']
  AND p.lifecycle_status = ANY(:lifecycleStatusArray)  -- ['NORMAL']
ORDER BY p.is_featured DESC, p.created_at DESC;

4. 统计总数 (分页用)

-- 统计符合条件的 ANY_DAY_TICKET 产品数量
SELECT COUNT(DISTINCT p.id) AS total
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
LEFT JOIN "PromoterProduct" pp ON pp.merchant_product_id = p.id AND pp.deleted_at IS NULL
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND p.merchant_id = :merchant_id;

5. 带变体信息的完整查询

-- 查询产品及其变体
SELECT
  p.id AS product_id,
  p.title,
  p.listing_type,
  p.status,
  p.inventory_quantity,
  p.at_door_ticket_config,
  -- 变体信息 (JSON 聚合)
  json_agg(
    json_build_object(
      'id', pv.id,
      'title', pv.title,
      'price', pv.price,
      'inventoryQuantity', pv.inventory_quantity,
      'fees', pv.fees,
      'transactionFee', pv.transaction_fee,
      'option', pv.option
    ) ORDER BY pv.position
  ) AS variants
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
LEFT JOIN "ProductVariant" pv ON pv.product_id = p.id
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND p.merchant_id = :merchant_id
GROUP BY
  p.id, p.title, p.listing_type, p.status, p.inventory_quantity, p.at_door_ticket_config
ORDER BY p.created_at DESC;

6. 带产品表单的查询 (KAT-8634)

-- 查询产品及其表单配置
SELECT
  p.id,
  p.title,
  p.is_product_form_enabled,
  -- 产品表单摘要
  json_build_object(
    'id', ucf.id,
    'title', ucf.title,
    'subtitle', ucf.subtitle,
    'fieldCount', (
      SELECT COUNT(*)
      FROM "ContactFormField" cff
      WHERE cff.user_contact_form_id = ucf.id
      AND cff.deleted_at IS NULL
    )
  ) AS product_form
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
LEFT JOIN "UserContactForm" ucf ON ucf.product_id = p.id AND ucf.form_type = 'PRODUCT'
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND p.merchant_id = :merchant_id;

7. 按库存数量排序查询

-- 按 inventory_quantity 升序或降序
SELECT
  p.id,
  p.title,
  p.inventory_quantity,
  p.sold_quantity
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND m.catalog_type = 'EVENT'
  AND p.merchant_id = :merchant_id
ORDER BY
  CASE
    WHEN :sortOrder = 'asc' THEN p.inventory_quantity
  END ASC,
  CASE
    WHEN :sortOrder = 'desc' THEN p.inventory_quantity
  END DESC;

8. 精选产品查询

-- 查询精选的 ANY_DAY_TICKET 产品
SELECT
  p.id,
  p.title,
  p.is_featured,
  p.featured_score
FROM "Product" p
INNER JOIN "Merchant" m ON p.merchant_id = m.id
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND p.deleted_at IS NULL
  AND p.is_featured = true
  AND m.catalog_type = 'EVENT'
  AND p.merchant_id = :merchant_id
ORDER BY
  p.featured_score DESC NULLS LAST,
  p.created_at DESC;

9. 查询可选活动列表 (用于 ANY_DAY_TICKET 购买时选择)

[!tip] 关键接口 对应 API: GET /product-event/v2/upcoming 用于前端展示可绑定的活动列表

-- 查询 UPCOMING 状态的活动（用于 ANY_DAY_TICKET Event 选择）
SELECT
  e.id,
  e.title,
  e.status,
  e.start_date,
  e.start_date_display,
  e.end_date_display,
  e.venue,
  e.location,
  e.poster,
  e.curator_id,
  -- 应用 atDoorTicketConfig 筛选规则
  CASE
    WHEN :includeTitleText IS NOT NULL
      AND e.title NOT ILIKE '%' || :includeTitleText || '%'
    THEN false
    WHEN :excludeTitleText IS NOT NULL
      AND e.title ILIKE '%' || :excludeTitleText || '%'
    THEN false
    WHEN :eventSelectionWindowHours IS NOT NULL
      AND e.start_date > now() + make_interval(hours => :eventSelectionWindowHours)
    THEN false
    ELSE true
  END AS is_eligible
FROM "Event" e
WHERE
  e.status = 'UPCOMING'  -- 仅查询即将到来的活动
  AND e.deleted_at IS NULL
  AND e.curator_id = :userId  -- 当前用户的活动
ORDER BY
  e.start_date ASC;  -- 按开始时间升序排列

10. 查询 ANY_DAY_TICKET 的已绑定 Event (通过订单)

-- 通过 OrderLineItem 查询 ANY_DAY_TICKET 已绑定的 Event
-- 用于统计、分析或验证
SELECT
  p.id AS product_id,
  p.title AS product_title,
  p.listing_type,
  e.id AS event_id,
  e.title AS event_title,
  e.status AS event_status,
  e.start_date AS event_start_date,
  COUNT(DISTINCT oli.id) AS order_count,
  SUM(oli.quantity) AS total_quantity_sold
FROM "Product" p
INNER JOIN "OrderLineItem" oli ON oli.product_id = p.id
INNER JOIN "Order" o ON o.id = oli.order_id
LEFT JOIN "Event" e ON e.id = (oli.variant_info->>'eventId')
WHERE
  p.listing_type = 'ANY_DAY_TICKET'
  AND oli.variant_info->>'eventId' IS NOT NULL  -- 仅查询已绑定 Event 的订单
  AND o.payment_status IN ('PAID', 'PARTIALLY_REFUNDED')  -- 已支付的订单
GROUP BY
  p.id, p.title, p.listing_type,
  e.id, e.title, e.status, e.start_date
ORDER BY
  e.start_date DESC;

注意事项

1. 权限要求

• 用户必须是 BUSINESS_PARTNER 角色
• 未登录用户返回 401 Unauthorized
• 权限不足返回 403 Forbidden

2. 时区处理

• timezone header 可选但推荐
• 所有时间戳以 UTC 返回
• 前端负责时区转换

3. 分页建议

• 建议每页返回 20-50 条数据
• 过大的 pageSize 可能导致响应缓慢

4. 搜索行为

• keyword 搜索范围：title、bodyHtml、tags、productType、vendor
• 搜索不区分大小写
• 支持部分匹配

5. 空结果处理

• 无匹配产品时返回 [[], 0]
• 非错误状态，应正常处理

6. Event 绑定流程（前端）

┌─────────────────────────────────────────────────────────────────┐
│  1. 列出 ANY_DAY_TICKET 产品（本接口）                          │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  2. 客户加入购物车 → 调用 GET /product-event/v2/upcoming         │
│     获取可选活动列表                                             │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  3. 客户选择活动 → POST /orders/updateCartItem                 │
│     (携带 eventId)                                              │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  4. 结账时 eventId 保留在 OrderLineItem.variantInfo.eventId     │
└─────────────────────────────────────────────────────────────────┘

相关接口

相关文档

• [[Product V2 Module]] - 产品 V2 模块文档
• [[KAT-10350-ANY-DAY-TICKET-DESIGN]] - ANY_DAY_TICKET 技术设计文档
• [[At-Door Ticket Config (KAT-10412)]] - 门票筛选配置说明
• [[Product Form (KAT-8634)]] - 产品表单功能

变更历史

附录

AtDoorTicketConfig 结构 (KAT-10412)

interface AtDoorTicketConfig {
  // 包含指定标题文本的活动才显示（null = 不限制）
  includeTitleText: string | null;

  // 排除指定标题文本的活动（null = 不限制）
  excludeTitleText: string | null;

  // 事件选择时间窗口（小时）
  // null = 显示所有未来活动
  eventSelectionWindowHours: number | null;
}

默认配置

const DEFAULT_AT_DOOR_TICKET_CONFIG: AtDoorTicketConfig = {
  includeTitleText: null,
  excludeTitleText: null,
  eventSelectionWindowHours: null
};